package com.itqf.dao.impl;

import com.itqf.connecter.DbConnection;
import com.itqf.dao.CarDao;
import com.itqf.dao.GoodsDao;
import com.itqf.entity.Car;
import com.itqf.entity.Goods;

import java.math.BigDecimal;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CarDaoImpl implements CarDao {
    @Override
    public Car hasCar(int userno, String gid){
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        Car car = null;
        conn= DbConnection.getConnection();
        try {
            sql = "SELECT * FROM car WHERE userno = ? and g_id =?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userno);
            ps.setInt(2,Integer.parseInt(gid));
            rs = ps.executeQuery();
            while(rs.next()) {
              int cid = rs.getInt(1);
                GoodsDao goodsDao = new GoodsDaoImpl();
                Goods goods = goodsDao.selectGoodsByGid(Integer.parseInt(gid));
              int number = rs.getInt(4);
              BigDecimal count = rs.getBigDecimal(5);
              car = new Car(cid,userno,Integer.parseInt(gid),goods,number,count);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally{
            DbConnection.close(rs, ps);}
        return car ;


    }

    @Override
    public void updateCar(Car car) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql = "update car set number = ? , count = ? where c_id = ?";
        try {

            ps = conn.prepareStatement(sql);
            ps.setInt(1,car.getNumber());
            ps.setBigDecimal(2,car.getCount());
            ps.setInt(3,car.getCid());
            rows = ps.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
    }

    @Override
    public void insertCar(Car car) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql = "insert into car(userno,g_id,number,count) value (?,?,?,?)";
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1,car.getUserno());
            ps.setInt(2,car.getGid());
            ps.setInt(3,car.getNumber());
            ps.setBigDecimal(4,car.getCount());
            rows = ps.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
    }

    @Override
    public List<Car> selectCarsByUserno(int userno){

        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        List<Car> list = new ArrayList<>();
        conn= DbConnection.getConnection();
        try {
            sql = "SELECT * FROM car WHERE userno = ? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1, userno);
            rs = ps.executeQuery();
            while(rs.next()) {
                int cid = rs.getInt(1);
                int gid = rs.getInt(3);
                GoodsDao goodsDao = new GoodsDaoImpl();
                Goods goods = goodsDao.selectGoodsByGid(gid);
                int number = rs.getInt(4);
                BigDecimal count = rs.getBigDecimal(5);
                Car car = new Car(cid,userno,gid,goods,number,count);
                list.add(car);
            }
        }catch(Exception e) {
            e.printStackTrace();
        }finally{
            DbConnection.close(rs, ps);}
        return list;
    }

    @Override
    public void deleteCarByCid(String cid){
        String sql = "delete from car where c_id = ?;";
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;
        conn= DbConnection.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, Integer.parseInt(cid));
            result = ps.executeUpdate();
        }catch(Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);

    }

    @Override
    public void updateByCid(BigDecimal count, String number, String cid) throws SQLException {
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String sql = null;
        int rows = 0;
        conn= DbConnection.getConnection();
        sql ="update car set count = ? , number = ? where c_id = ?;";
        try {
            ps = conn.prepareStatement(sql);
            ps.setBigDecimal(1,count);
            ps.setInt(2,Integer.valueOf(number));
            ps.setInt(3,Integer.valueOf(cid));

            rows = ps.executeUpdate();
        }
        catch (Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
    }

    @Override
    public void clearCarByUserno(String userno){
        String sql = "delete from car where userno = ?;";
        Connection conn=null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;
        conn= DbConnection.getConnection();
        try {
            ps = conn.prepareStatement(sql);
            ps.setInt(1, Integer.parseInt(userno));
            result = ps.executeUpdate();
        }catch(Exception e) {
            e.printStackTrace();
        }
        DbConnection.close(rs, ps);
    }
}
